/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;
import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.SupplierInterface;
import Objects.GroupSupplier;
import Objects.Supplier;
import WS.Proveedor;
import WS.Respuestas.RespBdProveedor;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author ARMGARCES
 */
public class BdProveedor extends Bd{
    private String mensj;
    /**
     * 
     */
    public BdProveedor(){
       
    }  
    /**
     * 
     * @param value
     * @return 
     */
    private String selectLoadSP(SupplierInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Supplier":
                return "{call sp_get_supplier_all}";
            case "GroupSupplier":
                return "{call sp_get_group_supplier_all}";               
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(SupplierInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Supplier":
                params = new String[]{"@find"};
                return "{call sp_get_supplier_find(?)}";
            case "GroupSupplier":
                params = new String[]{"@grp_supplier"};
                return "{call sp_get_group_supplier_find(?)}";               
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectCheckSP(int value){
        switch(value){
            case Proveedor.CHECK_SUPPLIER:
                params = new String[]{"@rif_prov"};
                return "{call sp_get_supplier_check(?,?,?)}";
            case Proveedor.CHECK_GROUP:
                params = new String[]{"@grp_supplier"};
                return "{call sp_get_group_supplier_check(?,?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectChangeSP(SupplierInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Supplier":
                mensj = "deleteSupplier:";
                params = new String[]{"@rif_prov","@status"};
                return "{call sp_upd_prov_status(?,?)}";
            case "GroupSupplier":
                mensj = "deleteGroupSupplier:";
                params = new String[]{"@grp_supplier","@status"};
                return "{call sp_upd_group_supplier_status(?,?)}";               
            default:
                return "";
        }
    }
    /***************************************************************************/
    /******************************** SUPPLIER *********************************/
    /***************************************************************************/    
    /**
     * 
     * @param value
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public SupplierInterface[] load(SupplierInterface value) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectLoadSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                ResultSet result = cstmt.executeQuery();            

                List<SupplierInterface> list = new ArrayList<>();
                while(result.next()) {
                   SupplierInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new SupplierInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param value
     * @param find
     * @return 
     * @throws Interfaces.BdException 
     * @throws java.sql.SQLException 
     */
    public SupplierInterface[] find(SupplierInterface value,String find) throws BdException, SQLException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();        
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectFindSP(value));  
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString(params[0], find);
                ResultSet result = cstmt.executeQuery();            
                List<SupplierInterface> list = new ArrayList<>();
                while(result.next()) {
                   SupplierInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new SupplierInterface[list.size()]);
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param check
     * @param value
     * @return 
     * @throws java.sql.SQLException
     * @throws Interfaces.BdException
     */
    public RespBdProveedor check(int check,String value) throws SQLException, BdException {        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();                    
            if (connection != null){                
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectCheckSP(check));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString(params[0], value);
                cstmt.registerOutParameter("result", java.sql.Types.INTEGER);       
                if(check == Proveedor.CHECK_SUPPLIER)
                    cstmt.registerOutParameter("seniat", java.sql.Types.INTEGER);               
                cstmt.execute();               
                if(check == Proveedor.CHECK_SUPPLIER)
                    return new RespBdProveedor(cstmt.getInt("result"),cstmt.getInt("seniat"));               
                else
                    return new RespBdProveedor(cstmt.getInt("result"));               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param value
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean change(String user,SupplierInterface value) throws SQLException, BdException{                        
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);            
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectChangeSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
                cstmt.setObject(params[0], value.getValue());
                int stat = 0;
                if(value.getStatus() == 0){
                    stat = 1;
                }            
                cstmt.setInt(params[1], stat );
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,String.valueOf(value.getId()),mensj);            
                connection.commit();   
                return true;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }            
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param supplier
     * @return
     * @throws SQLException 
     * @throws Interfaces.BdException 
     */
    public boolean saveSupplier(String user, int operacion, Supplier supplier) throws SQLException, BdException {                       
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";            
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_prov_data(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nuevo Proveedor:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_prov_basic(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Actualizando Proveedor:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                cstmt.setString("@rif_prov"         , supplier.getRif().toUpperCase()  );
                cstmt.setInt("@rif_val"             , supplier.getRif_val());
                cstmt.setInt("@grp_prov"            , supplier.getGroupsupplier().getIdGroupSupplier());
                cstmt.setString("@nombre"           , supplier.getNombre().toUpperCase());
                cstmt.setString("@rlegal"           , supplier.getRlegal());
                cstmt.setInt("@id_ciudad"           , supplier.getCity().getIdPoblacion());
                cstmt.setInt("@id_parroquia"        , supplier.getParish().getIdPoblacion());
                cstmt.setInt("@id_municipio"        , supplier.getMunicipality().getIdPoblacion());
                cstmt.setInt("@id_estado"           , supplier.getState().getIdPoblacion());
                cstmt.setInt("@id_pais"             , supplier.getCountry().getIdPoblacion());
                cstmt.setString("@direccion"        , supplier.getDireccion().toLowerCase());
                cstmt.setString("@telefonos"        , supplier.getTelefonos());
                cstmt.setString("@fax"              , supplier.getFax());
                cstmt.setString("@correo"           , supplier.getCorreo().toLowerCase());
                cstmt.setString("@contacto"         , supplier.getContacto());
                cstmt.setString("@celular"          , supplier.getCelular());
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,supplier.getIdSupplier() + "",mensj);            
                connection.commit();            
                return true;
            }else{
               throw new BdException("Error: Connexion BD no activa");
            }
        }catch(SQLException e){            
            connection.rollback();   
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param groupsupplier
     * @return
     * @throws SQLException
     * @throws Interfaces.BdException 
     */
    public boolean saveGroupSupplier(String user, int operacion, GroupSupplier groupsupplier) throws SQLException, BdException {                
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";            
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_group_supplier(?, ?)}");
                        mensj = "Nuevo Grupo de Proveedor:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_group_supplier_basic(?, ?, ?)}");
                        mensj = "Actualizando Grupo de Proveedor:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , groupsupplier.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , groupsupplier.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@grp_supplier"    , groupsupplier.getIdGroupSupplier() );
                        cstmt.setString("@nombre"       , groupsupplier.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , groupsupplier.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,groupsupplier.getIdGroupSupplier() + "",mensj);            
                connection.commit();
                return true;
            }else{
               throw new BdException("Error: Connexion BD no activa");
            }
        }catch(SQLException e){            
            connection.rollback();   
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdProveedor INSTANCE = new BdProveedor();
    }    
    public static BdProveedor getInstance() {
        return BdProveedor.BdHolder.INSTANCE;
    }
}
